import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.PrintStream;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Scanner;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import jxl.JXLException;
import jxl.Workbook;
import jxl.format.UnderlineStyle;
import jxl.write.Alignment;
import jxl.write.Border;
import jxl.write.BorderLineStyle;
import jxl.write.Label;
import jxl.write.VerticalAlignment;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class DmLog
{
  String jdbcString = "dm.jdbc.driver.DmDriver";
  String urlString = "jdbc:dm://localhost:5236";
  String port = "5236";
  String userName = "SYSDBA";
  String password = "SYSDBA";
  static Connection conn = null;
  static Connection conn1 = null;
  static int time = 1000;
  static int num = 100;
  static int tmp = 0;
  static int tmp1 = 0;
  static int para = 0;
  
  String port1 = "5236";
  String urlString1 = "jdbc:dm://10.12.xx.xxx";
  String userName1 = "userName";
  String password1 = "password";
  //system.out.println(password1);
  
  static analysisplan pp=new analysisplan();

  
  private static boolean match(String regex, String str)
  {
    Pattern pattern = Pattern.compile(regex);
    Matcher matcher = pattern.matcher(str);
    return matcher.lookingAt();
  }
  
  public static boolean isDate(String str)
  {
    String regex = "^((((1[6-9]|[2-9]\\d)\\d{2})-(0?[13578]|1[02])-(0?[1-9]|[12]\\d|3[01]))|(((1[6-9]|[2-9]\\d)\\d{2})-(0?[13456789]|1[012])-(0?[1-9]|[12]\\d|30))|(((1[6-9]|[2-9]\\d)\\d{2})-0?2-(0?[1-9]|1\\d|2[0-8]))|(((1[6-9]|[2-9]\\d)(0[48]|[2468][048]|[13579][26])|((16|[2468][048]|[3579][26])00))-0?2-29-)) (20|21|22|23|[0-1]?\\d):[0-5]?\\d:[0-5]?\\d$";
    return match(regex, str);
  }
  
  public static boolean isNumeric(String str)
  {
    if (str == null) {
      return false;
    }
    int sz = str.length();
    for (int i = 0; i < sz; i++) {
      if ((!Character.isDigit(str.charAt(i))) && (str.charAt(i) != '.') && ((i != 0) || (str.charAt(i) != '-'))) {
        return false;
      }
    }
    return true;
  }
  
  public static int getWordCount(String s)
  {
    int length = 0;
    for (int i = 0; i < s.length(); i++)
    {
      int ascii = Character.codePointAt(s, i);
      if ((ascii >= 0) && (ascii <= 255)) {
        length++;
      } else {
        length += 2;
      }
    }
    return length;
  }
  
  public static String replaceWenhao(String s, String p)
  {
    String temp = null;
    if (p == null) {
      return s;
    }
    if (p.indexOf("?") > 0) {
      return "/*参数中包含问号*/ " + s;
    }
    if (p == "参数过长") {
      return "/*参数过长*/ " + s;
    }
    if (p == "找不到有效的参数") {
      return "/*找不到有效的参数*/ " + s;
    }
    if ((p.indexOf("}") < 0) || (p.indexOf("{") < 0)) {
      return "/*找不到有效的参数*/ " + s;
    }
    while (s.indexOf("?") != -1)
    {
      if (p.indexOf("), (") > -1)
      {
        temp = p.substring(0, p.indexOf("), ("));
        p = p.substring(p.indexOf("), (") + 2, p.length());
        temp = temp.substring(temp.lastIndexOf(",") + 1, temp.length());
      }
      else
      {
        temp = p;
        temp = temp.substring(temp.lastIndexOf(",") + 1, temp.length() - 2);
        s = s.replaceFirst("\\?", Matcher.quoteReplacement(temp.trim()));
        break;
      }
      s = s.replaceFirst("\\?", Matcher.quoteReplacement(temp.trim()));
    }
    return "/*参数已替换*/ " + s;
  }
  
  public void loadJdbcDriver()
    throws SQLException
  {
    try
    {
      Class.forName(this.jdbcString);
    }
    catch (ClassNotFoundException e)
    {
      throw new SQLException("Load JDBC Driver Error : " + e.getMessage());
    }
    catch (Exception ex)
    {
      throw new SQLException("Load JDBC Driver Error : " + 
        ex.getMessage());
    }
  }
  
  public void connect()
    throws SQLException
  {
    try
    {
      conn = DriverManager.getConnection(this.urlString, this.userName, this.password);
    }
    catch (SQLException e)
    {
      throw new SQLException("Connect to DM Server Error : " + 
        e.getMessage());
    }
  }
  
  public void connect1()
		    throws SQLException
		  {
		    try
		    {
		      conn1 = DriverManager.getConnection(this.urlString1, this.userName1, this.password1);
		    }
		    catch (SQLException e)
		    {
		      throw new SQLException("Connect to DM Server Error : " + 
		        e.getMessage());
		    }
		  }
  
  public void disConnect()
    throws SQLException
  {
    try
    {
      conn.close();
      conn1.close();
    }
    catch (SQLException e)
    {
      throw new SQLException("close connection error : " + e.getMessage());
    }
  }
  
  private int readFilePath(String path, String destDirName, int para)
    throws Exception
  {
    SinoDetect sinodetector = new SinoDetect();
    
    File file = new File(path);
    
    File[] array = file.listFiles();
    FileWriter fw = new FileWriter(destDirName + File.separator + "SQL_ERR_LOG.txt", true);
    FileWriter fw1 = new FileWriter(destDirName + File.separator + "DATA_ERR_LOG.txt", true);
    if (array.length > 0)
    {
      for (int i = 0; i < array.length; i++) {
        if (array[i].isFile())
        {
          if (array[i].getName().substring(array[i].getName().indexOf(".") + 1, array[i].getName().length()).equals("log"))
          {
            System.out.println("----------------分析文件：" + array[i].getName() + "----------------");
            readFileByLines(array[i].getPath(), destDirName, fw, fw1, sinodetector.detectEncoding(array[i]), para);
          }
          else
          {
            System.out.println("----------------忽略非SQL日志文件：" + array[i].getName() + "-------------");
          }
        }
        else if (array[i].isDirectory()) {
          readFilePath(array[i].getPath(), destDirName, para);
        }
      }
      fw.close();
      fw1.close();
      return 1;
    }
    return 0;
  }
  
  public static void insert(String fileName, int line, String tempString, ArrayList<String> list, int para1)
    throws SQLException
  {
    conn.setAutoCommit(false);
    
    String sqlstr = "insert into log_commit values(?,?,?,?,?,rtrim(?),?)";
    PreparedStatement stmt = conn.prepareStatement(sqlstr);
    //System.out.println(tempString);
    String euser = "";
    String para = null;
    String starttime = tempString.substring(0, 19);
    
    int first = tempString.substring(0, 19).indexOf('-');
    
    int second = tempString.substring(0, 19).lastIndexOf('-');
    if (first == second) {
      starttime = null;
    }
    String sess;
    if (tempString.indexOf("(") > tempString.indexOf(")"))
    {
      sess = "";
    }
    else
    {
      sess = tempString.substring(tempString.indexOf("("), tempString.indexOf(")") + 1);
      if (sess.indexOf("user:") > 0) {
        euser = sess.substring(sess.indexOf("user:") + 5, sess.indexOf("trxid:"));
      }
    }
    String str1;
    if (tempString.indexOf("(EP[") > 0)
    {
      str1 = tempString.substring(tempString.indexOf("]") + 2, tempString.length());
      str1 = str1.substring(str1.indexOf("]") + 2, str1.indexOf("EXECTIME:"));
    }
    else if (tempString.indexOf("]") > tempString.indexOf("EXECTIME:"))
    {
      System.out.println("###错乱的SQL:" + tempString);
      tempString = tempString.substring(tempString.indexOf("(ms)") + 4, tempString.length());
      str1 = tempString.substring(tempString.indexOf("]") + 2, tempString.indexOf("EXECTIME:"));
      System.out.println("###修正的SQL:" + tempString);
    }
    else
    {
      str1 = tempString.substring(tempString.indexOf("]") + 2, tempString.indexOf("EXECTIME:"));
    }
    String str2;
    if (str1.length() > 4000)
    {
      str2 = str1.substring(0, 4000);
      if (getWordCount(str2) > 4000) {
        str2 = str2.substring(0, 3000);
      }
    }
    else
    {
      str2 = str1;
    }
    String exetime = tempString.substring(tempString.indexOf("EXECTIME:") + 10, tempString.indexOf("(ms)"));
    if (!isNumeric(exetime)) {
      exetime = "";
    }
    if (str1.indexOf("?") > 0)
    {
      for (int i = list.size() - 1; i >= 0; i--) {
        if ((((String)list.get(i)).length() > 1) && (((String)list.get(i)).indexOf(sess) > 0) && (((String)list.get(i)).indexOf("PARAMS(SEQNO, TYPE, DATA)") > 0))
        {
          para = ((String)list.get(i)).substring(((String)list.get(i)).indexOf("PARAMS(SEQNO, TYPE, DATA)") + 26, ((String)list.get(i)).length());
          list.remove(i);
          break;
        }
      }
      if (para == null) {
        para = "找不到有效的参数";
      }
    }
    if ((para != null) && (para.length() > 4000)) {
      para = "参数过长";
    }
    if (para1 == 1)
    {
      str1 = replaceWenhao(str1, para);
      str2 = replaceWenhao(str2, para);
    }
    stmt.setString(1, starttime);
    stmt.setString(2, sess);
    stmt.setString(3, str1);
    String bp=(analysisplan.getExecutePlan(str1, conn1));
    stmt.setString(4, bp);
    //System.out.println(bp);
    stmt.setString(5, exetime);
    stmt.setString(6, str2);
    stmt.setString(7, euser);
    if ((starttime != null) && (sess != null)) {
      if ((starttime.length() > 30) || (sess.length() > 500))
      {
        System.out.println(starttime);
        System.out.println(sess);
      }
    }
    stmt.execute();
    stmt.close();
    if (line % 5000 == 0) {
      conn.commit();
    }
  }
  
  public static void readFileByLines(String fileName, String destDirName, FileWriter fw, FileWriter fw1, int Encode, int para)
    throws SQLException
  {
    File file = new File(fileName);
    ArrayList<String> list = new ArrayList<String>();
    

    BufferedReader reader = null;
    

    String tempString = null;
    String tempString1 = null;
    String tempString2 = null;
    try
    {
      String coding;
      if (Encode == 6) {
        coding = "UTF-8";
      } else {
        coding = "GBK";
      }
      InputStreamReader read = new InputStreamReader(new FileInputStream(file), coding);
      reader = new BufferedReader(read);
      


      int line = 1;
      int cnt = 0;
      while ((tempString = reader.readLine()) != null)
      {
        if (tempString.indexOf("PARAMS(SEQNO, TYPE, DATA)") > 0)
        {
          list.add(tempString);
          if (list.size() == 3000) {
            list.remove(0);
          }
        }
        if (tempString.indexOf("sess:") > 0) {
          cnt++;
        }
        if ((tempString.indexOf("sess:") < 0) && (tempString.length() > 0) && (cnt == 0) && (line >= 10))
        {
          System.out.println("无效的sql日志文件：" + fileName);
          break;
        }
        if ((tempString.length() > 1) && (tempString.indexOf("[ERR(") > 0) && ((tempString.indexOf("sess:") > 0) || (tempString.indexOf("(EP[") > 0)))
        {
          fw.write(tempString, 0, tempString.length());
          fw.write("\r\n\r\n");
          fw.flush();
        }
        
        if ((tempString.length() > 1) && (tempString.indexOf(") [") > 0) && (tempString.indexOf("[ORA]") < 0) && (tempString.indexOf("[ERR") < 0)) {
          if (tempString.indexOf("(sess") >= 0)
          {
        	  System.out.println("temp 001");
            if (tempString.indexOf("(EP[") <= 0) {}
          }
          else
          {
        	  System.out.println("temp 002");
            while ((tempString.indexOf("EXECTIME:") < 0) && (tempString.indexOf("(ms)") < 0))
            {
              tempString1 = reader.readLine();
              if (tempString1.indexOf("PARAMS(SEQNO, TYPE, DATA)") > 0)
              {
                list.add(tempString1);
                if (list.size() == 3000) {
                  list.remove(0);
                }
                tempString2 = "###无效的SQL " + line + "行附近：" + tempString;
                fw1.write(tempString2, 0, tempString2.length());
                fw1.write("\r\n\r\n");
                fw1.flush();
                tempString = null;
                
                break;
              }
              if (tempString1.length() > 19) {
                if ((isDate(tempString1.substring(0, 19))) && (tempString.indexOf("EXECTIME:") < 0) && (tempString.indexOf("(ms)") < 0))
                {
                  tempString2 = "###无效的SQL " + line + "行附近：" + tempString;
                  fw1.write(tempString2, 0, tempString2.length());
                  fw1.write("\r\n\r\n");
                  fw1.flush();
                  tempString = null;
                  break;
                }
              }
              if (tempString1 == null)
              {
                tempString2 = "###无效的SQL " + line + "行附近：" + tempString1;
                fw1.write(tempString2, 0, tempString2.length());
                fw1.write("\r\n\r\n");
                fw1.flush();
                tempString = null;
                
                break;
              }
              tempString = tempString + "  " + tempString1;
              if (tempString.lastIndexOf("ip:::ffff:") > tempString.indexOf("ip:::ffff:"))
              {
                tempString2 = "###无效的SQL " + line + "行附近：" + tempString.substring(0, tempString.lastIndexOf("ip:::ffff:") - tempString.indexOf("ip:::ffff:"));
                if ((tempString2.indexOf("[ORA]") < 0) && (tempString2.indexOf(") [") > 0) && (tempString2.indexOf("[ERR") < 0))
                {
                  fw1.write(tempString2, 0, tempString2.length());
                  fw1.write("\r\n\r\n");
                  fw1.flush();
                }
                tempString = tempString.substring(tempString.lastIndexOf("ip:::ffff:") - tempString.indexOf("ip:::ffff:"), tempString.length());
              }
              line++;
            }
            
            //System.out.println(tempString);
            if (tempString != null) {
              insert(fileName, line, tempString, list, para);
            }
          }
        }
        line++;
        if (line % 5000 == 0) {
          System.out.println(fileName + "文件已处理：" + line + "行");
        }
      }
      conn.commit();
      

      reader.close();
    }
    catch (IOException e)
    {
      e.printStackTrace();
      if (reader != null) {
        try
        {
          reader.close();
        }
        catch (IOException localIOException1) {}
      }
    }
    finally
    {
      if (reader != null) {
        try
        {
          reader.close();
        }
        catch (IOException localIOException2) {}
      }
    }
  }
  
  private static void displayResultSet(ResultSet rs)
    throws SQLException
  {
    ResultSetMetaData rsmd = rs.getMetaData();
    
    int numCols = rsmd.getColumnCount();
    for (int i = 1; i <= numCols; i++)
    {
      if (i > 1) {
        System.out.print(",");
      }
      System.out.print(rsmd.getColumnLabel(i));
    }
    System.out.println("");
    while (rs.next())
    {
      for (int i = 1; i <= numCols; i++)
      {
        if (i > 1) {
          System.out.print(",");
        }
        System.out.print(rs.getString(i));
      }
      System.out.println("");
    }
  }
  
  public void createTable()
    throws SQLException
  {
    int count = 0;
    String sql1 = "select count(*) from user_tables where table_name='LOG_COMMIT'";
    

    String sql2 = "create table log_commit(starttime varchar(30),sess varchar(500),sqlstr text,sqlplan text,exetime float,sqlstr_sub varchar(8000),euer varchar(100));";
    
    String sql3 = "DROP TABLE LOG_COMMIT";
    


    Statement stmt = conn.createStatement();
    
    ResultSet rs = stmt.executeQuery(sql1);
    
    rs.next();
    count = rs.getInt(1);
    if (count == 0)
    {
      stmt.execute(sql2);
    }
    else
    {
      stmt.execute(sql3);
      stmt.execute(sql2);
    }
    rs.close();
    stmt.close();
  }
  
  public static void writeExcelTime(String fileName, int num2, String destDirName)
    throws SQLException, JXLException, IOException
  {
    WritableWorkbook wwb = null;
    try
    {
      wwb = Workbook.createWorkbook(new File(destDirName + File.separator + fileName));
    }
    catch (IOException e)
    {
      e.printStackTrace();
    }
    int charTitle = 12;
    int charNormal = 10;
    
    WritableFont titleFont = new WritableFont(
      WritableFont.createFont("宋体"), charTitle, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.WHITE);
    WritableCellFormat titleFormat = new WritableCellFormat(
      titleFont);
    titleFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
    titleFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
    titleFormat.setAlignment(Alignment.CENTRE);
    titleFormat.setWrap(true);
    titleFormat.setBackground(jxl.write.Colour.BLUE_GREY);
    
    WritableFont normalFont = new WritableFont(
      WritableFont.createFont("宋体"), charNormal);
    
    WritableCellFormat normalFormat = new WritableCellFormat(
      normalFont);
    normalFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
    normalFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
    normalFormat.setAlignment(Alignment.CENTRE);
    normalFormat.setWrap(false);
    


    WritableCellFormat normalFormat1 = new WritableCellFormat(
      normalFont);
    normalFormat1.setBorder(Border.ALL, BorderLineStyle.THIN);
    normalFormat1.setVerticalAlignment(VerticalAlignment.CENTRE);
    normalFormat1.setAlignment(Alignment.LEFT);
    normalFormat1.setWrap(false);
    

    WritableCellFormat normalFormat2 = new WritableCellFormat(
      normalFont);
    normalFormat2.setBorder(Border.ALL, BorderLineStyle.THIN);
    normalFormat2.setVerticalAlignment(VerticalAlignment.CENTRE);
    normalFormat2.setAlignment(Alignment.LEFT);
    normalFormat2.setWrap(false);
    normalFormat2.setBackground(jxl.write.Colour.VERY_LIGHT_YELLOW);
    

    int count = 0;
    

    String sql1 = " select   count(distinct sqlstr_sub) from   log_commit where exetime >= " + 
      num2;
    

    String sql2 = " select s,max_tim,min_tim,CEILING(avg_90) avg90,CEILING(avg_tim) avg100,cnt from (  select          s ,          max(tim)over(partition by sub) max_tim,          min(tim)over(partition by sub) min_tim,          avg(tim)over(partition by sub) avg_tim,          avg(case when tile<=9 then tim else null end)over(partition by sub) avg_90,         count(1)over(partition by sub) cnt,          row_number()over(partition by sub order by tim desc) r   from           (                  select                            ifnull(sqlstr,cast(sqlstr_sub as clob)) s     ,                          sqlstr_sub sub,                          exetime tim,                          ntile(10) over(partition by sqlstr_sub order by exetime) tile                 from                          log_commit                  where                          exetime >=  " + 
    

















      num2 + 
      "         ) t1" + 
      ")  where r = 1 order by  max_tim desc";
    


    Statement stmt = conn.createStatement();
    

    ResultSet rs = stmt.executeQuery(sql1);
    

    rs.next();
    count = rs.getInt(1);
    
    rs.close();
    stmt.close();
    

    Statement stmt2 = conn.createStatement();
    

    ResultSet rs2 = stmt2.executeQuery(sql2);
    if (wwb != null)
    {
      WritableSheet ws = wwb.createSheet("SQL分析", 0);
      
      ws.setColumnView(0, 10);
      ws.setColumnView(1, 100);
      ws.setColumnView(2, 18);
      ws.setColumnView(3, 18);
      ws.setColumnView(4, 20);
      ws.setColumnView(5, 18);
      ws.setColumnView(6, 15);
      ws.setColumnView(7, 20);
      ws.setColumnView(8, 15);
      
      Label tit0 = new Label(0, 0, "序号", titleFormat);
      Label tit1 = new Label(1, 0, "SQL语句", titleFormat);
      Label tit2 = new Label(2, 0, "最大执行时间", titleFormat);
      Label tit3 = new Label(3, 0, "最小执行时间", titleFormat);
      Label tit5 = new Label(4, 0, "90%平均执行时间", titleFormat);
      Label tit4 = new Label(5, 0, "平均执行时间", titleFormat);
      Label tit6 = new Label(6, 0, "执行次数", titleFormat);
      Label tit7 = new Label(7, 0, "优化方法", titleFormat);
      Label tit8 = new Label(8, 0, "优化后时间", titleFormat);
      try
      {
        ws.addCell(tit0);
        ws.addCell(tit1);
        ws.addCell(tit2);
        ws.addCell(tit3);
        ws.addCell(tit4);
        ws.addCell(tit5);
        ws.addCell(tit6);
        ws.addCell(tit7);
        ws.addCell(tit8);
      }
      catch (RowsExceededException e)
      {
        e.printStackTrace();
      }
      catch (WriteException e)
      {
        e.printStackTrace();
      }
      FileInputStream fis = null;
      InputStreamReader isr = null;
      BufferedReader br = null;
      
      FileOutputStream fos = null;
      PrintWriter pw = null;
      for (int i = 1; i <= count; i++)
      {
        rs2.next();
        for (int j = 0; j <= 6; j++)
        {
          Label labelC = null;
          if (j == 0) {
            labelC = new Label(j, i, String.valueOf(i), normalFormat);
          } else if (j == 1)
          {
            if (rs2.getString(j).length() > 30000)
            {
              SQLForm sqlForm = new SQLForm();
              sqlForm.setCase(false, false);
              sqlForm.setLowerCase(false);
              sqlForm.setGraphLevel(false);
              sqlForm.setSuppressSpace(true);
              sqlForm.setQuoteCharacter("'");
              sqlForm.setSuppressEmptyLine(true);
              sqlForm.setFormatLanguage("SQL");
              sqlForm.setBracketSpaces("noSpacesAroundBracker");
              sqlForm.setCommaSpaces("oneSpaceAfterComma");
              sqlForm.setAlignmentEqual(!rs2.getString(j).contains("!="));
              try
              {
                File filename = new File(destDirName + File.separator + "Q" + i + ".txt");
                if (!filename.exists()) {
                  filename.createNewFile();
                }
                fis = new FileInputStream(filename);
                isr = new InputStreamReader(fis, "GBK");
                br = new BufferedReader(isr);
                StringBuffer buf = new StringBuffer();
                buf = buf.append(sqlForm.formatSQLAsString(rs2.getString(j)));
                
                fos = new FileOutputStream(filename);
                pw = new PrintWriter(fos);
                pw.write(buf.toString().toCharArray());
                pw.flush();
              }
              catch (IOException e1)
              {
                throw e1;
              }
              finally
              {
                if (pw != null) {
                  pw.close();
                }
                if (fos != null) {
                  fos.close();
                }
                if (br != null) {
                  br.close();
                }
                if (isr != null) {
                  isr.close();
                }
                if (fis != null) {
                  fis.close();
                }
              }
              labelC = new Label(j, i, rs2.getString(j).substring(0, 30000), normalFormat2);
            }
            else
            {
              labelC = new Label(j, i, rs2.getString(j), normalFormat1);
            }
          }
          else {
            labelC = new Label(j, i, rs2.getString(j), normalFormat);
          }
          try
          {
            ws.addCell(labelC);
          }
          catch (RowsExceededException e)
          {
            e.printStackTrace();
          }
          catch (WriteException e)
          {
            e.printStackTrace();
          }
        }
      }
      rs2.close();
      stmt2.close();
      try
      {
        wwb.write();
        
        wwb.close();
      }
      catch (IOException e)
      {
        e.printStackTrace();
      }
      catch (WriteException e)
      {
        e.printStackTrace();
      }
    }
  }
  
  public static void writeExcelNum(String fileName, int num2, String destDirName)
    throws SQLException, JXLException, IOException
  {
    WritableWorkbook wwb = null;
    try
    {
      wwb = Workbook.createWorkbook(new File(destDirName + File.separator + fileName));
    }
    catch (IOException e)
    {
      e.printStackTrace();
    }
    int charTitle = 12;
    int charNormal = 10;
    
    WritableFont titleFont = new WritableFont(
      WritableFont.createFont("宋体"), charTitle, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.WHITE);
    WritableCellFormat titleFormat = new WritableCellFormat(
      titleFont);
    titleFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
    titleFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
    titleFormat.setAlignment(Alignment.CENTRE);
    titleFormat.setWrap(true);
    titleFormat.setBackground(jxl.write.Colour.BLUE_GREY);
    
    WritableFont normalFont = new WritableFont(
      WritableFont.createFont("宋体"), charNormal);
    
    WritableCellFormat normalFormat = new WritableCellFormat(
      normalFont);
    normalFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
    normalFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
    normalFormat.setAlignment(Alignment.CENTRE);
    normalFormat.setWrap(false);
    


    WritableCellFormat normalFormat1 = new WritableCellFormat(
      normalFont);
    normalFormat1.setBorder(Border.ALL, BorderLineStyle.THIN);
    normalFormat1.setVerticalAlignment(VerticalAlignment.CENTRE);
    normalFormat1.setAlignment(Alignment.LEFT);
    normalFormat1.setWrap(false);
    

    WritableCellFormat normalFormat2 = new WritableCellFormat(
      normalFont);
    normalFormat2.setBorder(Border.ALL, BorderLineStyle.THIN);
    normalFormat2.setVerticalAlignment(VerticalAlignment.CENTRE);
    normalFormat2.setAlignment(Alignment.LEFT);
    normalFormat2.setWrap(false);
    normalFormat2.setBackground(jxl.write.Colour.VERY_LIGHT_YELLOW);
    

    int count = 0;
    

    String sql1 = " select  count(*) from (    select 1 from log_commit group by sqlstr_sub having count(*) >= ?);";
    






















    String sql2 = " select s,max_tim,min_tim,CEILING(avg_90) avg90,CEILING(avg_tim) avg100,cnt from (  select          s ,          max(tim)over(partition by sub) max_tim,          min(tim)over(partition by sub) min_tim,          avg(tim)over(partition by sub) avg_tim,          avg(case when tile<=9 then tim else null end)over(partition by sub) avg_90,          count(1)over(partition by sub) cnt,          row_number()over(partition by sub order by tim desc) r   from           (                  select                             ifnull(sqlstr,cast(sqlstr_sub as clob)) s     ,                          sqlstr_sub sub,                          exetime tim,                          ntile(10) over(partition by sqlstr_sub order by exetime) tile                 from                          log_commit          ) t1         )  where r = 1 and cnt>=? order by  cnt desc";
    



















    PreparedStatement stmt = conn.prepareStatement(sql1);
    stmt.setInt(1, num2);
    
    ResultSet rs = stmt.executeQuery();
    

    rs.next();
    count = rs.getInt(1);
    
    rs.close();
    stmt.close();
    


    PreparedStatement stmt2 = conn.prepareStatement(sql2);
    stmt2.setInt(1, num2);
    
    ResultSet rs2 = stmt2.executeQuery();
    if (wwb != null)
    {
      WritableSheet ws = wwb.createSheet("SQL分析", 0);
      
      ws.setColumnView(0, 10);
      ws.setColumnView(1, 100);
      ws.setColumnView(2, 18);
      ws.setColumnView(3, 18);
      ws.setColumnView(4, 20);
      ws.setColumnView(5, 18);
      ws.setColumnView(6, 15);
      ws.setColumnView(7, 20);
      ws.setColumnView(8, 15);
      
      Label tit0 = new Label(0, 0, "序号", titleFormat);
      Label tit1 = new Label(1, 0, "SQL语句", titleFormat);
      Label tit2 = new Label(2, 0, "最大执行时间", titleFormat);
      Label tit3 = new Label(3, 0, "最小执行时间", titleFormat);
      Label tit5 = new Label(4, 0, "90%平均执行时间", titleFormat);
      Label tit4 = new Label(5, 0, "平均执行时间", titleFormat);
      Label tit6 = new Label(6, 0, "执行次数", titleFormat);
      Label tit7 = new Label(7, 0, "优化方法", titleFormat);
      Label tit8 = new Label(8, 0, "优化后时间", titleFormat);
      try
      {
        ws.addCell(tit0);
        ws.addCell(tit1);
        ws.addCell(tit2);
        ws.addCell(tit3);
        ws.addCell(tit4);
        ws.addCell(tit5);
        ws.addCell(tit6);
        ws.addCell(tit7);
        ws.addCell(tit8);
      }
      catch (RowsExceededException e)
      {
        e.printStackTrace();
      }
      catch (WriteException e)
      {
        e.printStackTrace();
      }
      FileInputStream fis = null;
      InputStreamReader isr = null;
      BufferedReader br = null;
      
      FileOutputStream fos = null;
      PrintWriter pw = null;
      for (int i = 1; i <= count; i++)
      {
        rs2.next();
        for (int j = 0; j <= 6; j++)
        {
          Label labelC = null;
          if (j == 0) {
            labelC = new Label(j, i, String.valueOf(i), normalFormat);
          } else if (j == 1)
          {
            if (rs2.getString(j).length() > 10000)
            {
              SQLForm sqlForm = new SQLForm();
              sqlForm.setCase(false, false);
              sqlForm.setLowerCase(false);
              sqlForm.setGraphLevel(false);
              sqlForm.setSuppressSpace(true);
              sqlForm.setQuoteCharacter("'");
              sqlForm.setSuppressEmptyLine(true);
              sqlForm.setFormatLanguage("SQL");
              sqlForm.setBracketSpaces("noSpacesAroundBracker");
              sqlForm.setCommaSpaces("oneSpaceAfterComma");
              sqlForm.setAlignmentEqual(!rs2.getString(j).contains("!="));
              try
              {
                File filename = new File(destDirName + File.separator + "Q" + i + ".txt");
                if (!filename.exists()) {
                  filename.createNewFile();
                }
                fis = new FileInputStream(filename);
                isr = new InputStreamReader(fis, "GBK");
                br = new BufferedReader(isr);
                StringBuffer buf = new StringBuffer();
                buf = buf.append(sqlForm.formatSQLAsString(rs2.getString(j)));
                
                fos = new FileOutputStream(filename);
                pw = new PrintWriter(fos);
                pw.write(buf.toString().toCharArray());
                pw.flush();
              }
              catch (IOException e1)
              {
                throw e1;
              }
              finally
              {
                if (pw != null) {
                  pw.close();
                }
                if (fos != null) {
                  fos.close();
                }
                if (br != null) {
                  br.close();
                }
                if (isr != null) {
                  isr.close();
                }
                if (fis != null) {
                  fis.close();
                }
              }
              labelC = new Label(j, i, rs2.getString(j).substring(0, 10000), normalFormat2);
            }
            else
            {
              labelC = new Label(j, i, rs2.getString(j), normalFormat1);
            }
          }
          else {
            labelC = new Label(j, i, rs2.getString(j), normalFormat);
          }
          try
          {
            ws.addCell(labelC);
          }
          catch (RowsExceededException e)
          {
            e.printStackTrace();
          }
          catch (WriteException e)
          {
            e.printStackTrace();
          }
        }
      }
      rs2.close();
      stmt2.close();
      try
      {
        wwb.write();
        
        wwb.close();
      }
      catch (IOException e)
      {
        e.printStackTrace();
      }
      catch (WriteException e)
      {
        e.printStackTrace();
      }
    }
  }
  
  public static void main(String[] args)
    throws Exception
  {
    Calendar cal = Calendar.getInstance();
    
    int hour = cal.get(11);
    int minute = cal.get(12);
    int ss = cal.get(13);
    


    Scanner s = new Scanner(System.in);
    

    DmLog dmLog = new DmLog();
    System.out.println("############################# dm7日志分析程序使用说明 ########################");
    System.out.println("###                                                                        ###");
    System.out.println("### 1.请确认sql trace参数，确保每条语句后紧跟sql语句时间: 1:25             ###");
    System.out.println("### 2.本程序建表log_commit进行分析                                         ###");
    System.out.println("### 3.本程序建表前会删除同名表，请做好备份 ！                              ###");
    System.out.println("### 4.请使用页大小为32k的DM7进行分析 ！                                    ###");
    System.out.println("### 5.结果中sql语句背景为黄色的表示sql长度超过30000,已截断 ！              ###");
    System.out.println("### 6.截断的语句会保存到文本文件中，如第一条截断会生成Q1.txt ！            ###");
    System.out.println("### 7.本程序生成的所有文件存放在当前目录下的RESULT_$DATE目录下 ！          ###");
    System.out.println("### 8.本程序只能连测试环境进行分析，切忌连生产环境！！！                   ###");
    System.out.println("###                                                                        ###");
    System.out.println("############################# 说明完毕，请使用！##############################");
    System.out.println("");
    System.out.println("使用本机默认DM7数据源请输入0，指定数据源请输入1：");
    //tmp1 = 0;//s.nextInt();
    tmp1 = s.nextInt();
    if (tmp1 == 1)
    {
      Scanner sc = new Scanner(System.in);
      System.out.println("请输入IP地址：");
      String ip = sc.nextLine();
      if (ip.length() > 0) {
        dmLog.urlString = ("jdbc:dm://" + ip);
      } else {
        dmLog.urlString = ("jdbc:dm://" + "localhost");
      }
      System.out.println("请输入端口号：");
      String port = sc.nextLine();
      if (port.length() > 0) {
        dmLog.urlString = (dmLog.urlString + ":" + port);
      } else {
        dmLog.urlString = (dmLog.urlString + ":" + "5236");
      }
      System.out.println("请输入用户名：");
      String username = sc.nextLine();
      if (username.length() > 0) {
        dmLog.userName = username;
      }
      System.out.println("请输入密码：");
      String pwd = sc.nextLine();
      if (pwd.length() > 0) {
        dmLog.password = pwd;
      }
    }
    System.out.println("根据日志入库生成分析结果请输入0，根据表中已有数据直接生成分析结果请输入1：");
    //tmp = 0;//s.nextInt();
    tmp = s.nextInt();
    
    dmLog.loadJdbcDriver();
    dmLog.connect();
    dmLog.connect1();
    
    String destDirName = null;
    SimpleDateFormat df = new SimpleDateFormat("yyyy_MM_dd");
    destDirName = df.format(new Date());
    destDirName = "RESULT_" + destDirName + "_" + hour + "_" + minute + "_" + ss;
    
    File dir = new File(destDirName);
    

    int ret = 0;
    if (tmp == 0)
    {
    	Scanner sc1 = new Scanner(System.in);
      System.out.println("请输入存放SQL日志的文件夹绝对路径,并保证文件夹中只有SQL日志文件：");
      //String path = "C:\\Users\\cumthqlOld\\Desktop\\1\\2";//sc1.nextLine();
      String path = sc1.nextLine();
      System.out.println("您想分析多少毫秒以上的SQL语句：");
      //time = 0;//s.nextInt();
      time = s.nextInt();
      System.out.println("您想分析执行多少次以上的SQL语句：");
      //num = 1;//s.nextInt();
      num = s.nextInt();
      System.out.println("您是否需要替换SQL语句中的参数：");
      //para = 1;//s.nextInt();
      para = s.nextInt();
      dmLog.createTable();
      if (dir.exists()) {
        System.out.println("创建目录" + destDirName + "失败，目标目录已经存在");
      }
      if (!destDirName.endsWith(File.separator)) {
        destDirName = destDirName + File.separator;
      }
      if (dir.mkdirs()) {
        System.out.println("创建目录" + destDirName.substring(0, destDirName.length() - 1) + "成功！");
      } else {
        System.out.println("创建目录" + destDirName.substring(0, destDirName.length() - 1) + "失败！");
      }
      ret = dmLog.readFilePath(path, destDirName, para);
      Statement stm1 = conn.createStatement();
      System.out.println("----------------创建索引---------------------");
      stm1.execute("create or replace index idx_exetime_" + destDirName.substring(0, destDirName.length() - 1) + " on LOG_COMMIT(exetime);");
      stm1.execute("SP_COL_STAT_INIT_EX(USER,'LOG_COMMIT','EXETIME',100);");
      System.out.println("----------------创建索引成功-------------------");
      stm1.close();
      if (ret == 1)
      {
        System.out.println("----------------入库完毕，开始分析---------------");
        //20161104只入库//writeExcelTime("more_than_" + time + "_ms_log_result.xls", time, destDirName);
      //20161104只入库//writeExcelNum("more_than_" + num + "_times_log_result.xls", num, destDirName);
      }
    }
    if (tmp == 1)
    {
      System.out.println("您想分析多少毫秒以上的SQL语句：");
      time = s.nextInt();
      System.out.println("您想分析执行多少次以上的SQL语句：");
      num = s.nextInt();
      
      System.out.println("----------------开始分析----------------");
      if (dir.exists()) {
        System.out.println("创建分析结果目录" + destDirName + "失败，目标目录已经存在");
      }
      if (!destDirName.endsWith(File.separator)) {
        destDirName = destDirName + File.separator;
      }
      if (dir.mkdirs()) {
        System.out.println("创建分析结果目录" + destDirName.substring(0, destDirName.length() - 1) + "成功！");
      } else {
        System.out.println("创建分析结果目录" + destDirName.substring(0, destDirName.length() - 1) + "失败！");
      }
    //20161104只入库//writeExcelTime("more_than_" + time + "_ms_log_result.xls", time, destDirName);
    //20161104只入库//writeExcelNum("more_than_" + num + "_times_log_result.xls", num, destDirName);
    }
    dmLog.disConnect();
    System.out.println("----------------分析完毕，请查看结果-------------");
  }
}
